package org.example.easyexceltest.controller;

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.cache.MapCache;
import com.alibaba.excel.cache.selector.SimpleReadCacheSelector;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.example.easyexceltest.entity.Other;
import org.example.easyexceltest.entity.User;
import org.example.easyexceltest.read.OtherDataListener;
import org.example.easyexceltest.read.UserDataListener;
import org.example.easyexceltest.service.OtherService;
import org.example.easyexceltest.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

@Controller
public class WebTest {


    @Autowired
    private UserService userService;

    @Autowired
    private OtherService otherService;

    /**
     * 文件下载 (模板文件或者造点假数用于导入）（失败了会返回一个有部分数据的Excel）
     * <p>
     * 1. 创建excel对应的实体对象
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();

            WriteSheet writeSheet1 = EasyExcel.writerSheet("user").head(User.class).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet("other").head(Other.class).build();


            writer.write(user(), writeSheet1);
            writer.write(other(), writeSheet2);
            writer.finish();
    }

    /**
     * 文件上传 读取文件里的数据并入库
     * <p>
     * 1. 创建excel对应的实体对象 参照
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照
     * <p>
     * 3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        ExcelReader read = EasyExcel.read(file.getInputStream()).readCache(new MapCache()).build();

        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(User.class).registerReadListener(new UserDataListener(userService)).build();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(Other.class).registerReadListener(new OtherDataListener(otherService)).build();

        read.read(readSheet1, readSheet2);
        return "success";
    }

    @GetMapping("export")
    public void export(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("导出结果", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        userService.limitSelect(response);
    }


    private List<User> user() {
        List<User> list = ListUtils.newArrayList();
        for (int i = 0; i < 100000; i++) {
            User data = new User();
            data.setAge(i%3);
            data.setCar(i%10);
            data.setRemark("123");
            data.setHobby("打篮球");
            data.setCompany("蓝十字");
            data.setSchool("清华大学");
            data.setDept("技术部");
            data.setSex(i%2);
            data.setCar(i%6);
            list.add(data);
        }
        return list;
    }

    private List<Other> other() {
        List<Other> list = ListUtils.newArrayList();
        for (int i = 0; i < 100000; i++) {
            Other data = new Other();
            data.setUserId(i%10 + 1);
            data.setT1("111");
            data.setT2("222");
            data.setT3("333");
            data.setT4("444");
            data.setT5("555");
            data.setT6("666");
            data.setT7("777");
            data.setT8("888");
            data.setT9("999");
            list.add(data);
        }
        return list;
    }
}
